﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace QLKS.DAL
{
    internal class SqlDataProvider
    {
        
        private string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["QLKSConnection"].ConnectionString;
        private SqlConnection con;
        
        public SqlDataProvider()
        {
            if (con == null)
                OpenConnection();
        }

        #region Private Methods
        
        private void OpenConnection()
        {
            con = new SqlConnection(this.connectionString);
        }
        private void CloseConnection()
        {
            con.Dispose();
        }
        private String FormatParameter(String name)
        {
            if (name.IndexOf("@").Equals(0))
                return name;
            return "@" + name;
        }
        private DbType Type2DbType(Type type)
        {
            if (type == typeof(Byte))
                return DbType.Byte;
            else if (type == typeof(Boolean))
                return DbType.Boolean;
            else if (type == typeof(String))
                return DbType.String;
            else if (type == typeof(Int16))
                return DbType.Int16;
            else if (type == typeof(Int32))
                return DbType.Int32;
            else if (type == typeof(Int64))
                return DbType.Int64;
            else if (type == typeof(UInt16))
                return DbType.UInt16;
            else if (type == typeof(UInt32))
                return DbType.UInt32;
            else if (type == typeof(UInt64))
                return DbType.UInt64;
            else if (type == typeof(Decimal) || type == typeof(Double) || type == typeof(float))
                return DbType.Decimal;
            else if (type == typeof(DateTime))
                return DbType.DateTime;
            return DbType.Object;
        }

        #endregion

        #region AddParameter & AddCommand
        
        private void AddParameter(SqlCommand cmd, SqlParameter parameter)
        {
            cmd.Parameters.Add(parameter);
        }
        
        private void AddParameter(SqlCommand cmd, string name, object value, StoredProcedureParameterDirection? parameterDirection)
        {
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = name;
            parameter.Value = value;
            //parameter.DbType = Type2DbType(value.GetType());
            if (parameterDirection.HasValue)
            {
                switch (parameterDirection.Value)
                {
                    case StoredProcedureParameterDirection.Input:
                        parameter.Direction = System.Data.ParameterDirection.Input;
                        break;
                    case StoredProcedureParameterDirection.Output:
                        parameter.Direction = System.Data.ParameterDirection.Output;
                        break;
                    case StoredProcedureParameterDirection.InputOutput:
                        parameter.Direction = System.Data.ParameterDirection.InputOutput;
                        break;
                    case StoredProcedureParameterDirection.ReturnValue:
                        parameter.Direction = System.Data.ParameterDirection.ReturnValue;
                        break;
                }
            }
            AddParameter(cmd, parameter);
        }
        
        private void AddParameter(SqlCommand cmd, string name, object value)
        {
            AddParameter(cmd, name, value, null);
        }
        
        private SqlCommand AddCommand(string spName, string[] Params, object[] Values)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = spName;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 900;

            for (int j = 0; j < Params.Length; j++)
                AddParameter(cmd, this.FormatParameter(Params[j].ToString()), Values[j]);
            if (con.State == System.Data.ConnectionState.Closed)
                con.Open();

            return cmd;
        }
        
        #endregion

        #region Execute Method

        public SqlDataReader ExecuteReader(string spName, string[] Params, object[] Values)
        {
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader = null;
            try
            {
                cmd = AddCommand(spName, Params, Values);
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex) { throw (ex); }
            finally
            {
                if (con.State == System.Data.ConnectionState.Open)
                    con.Close();
                cmd.Dispose();
            }
            return reader;
        }
        
        public DataTable ExecuteDataTable(string spName, string[] Params, object[] Values)
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            try
            {
                cmd = AddCommand(spName, Params, Values);
                adapter.SelectCommand = cmd;
                adapter.Fill(dt);
            }
            catch (Exception ex) { throw (ex); }
            finally
            {
                if (con.State == System.Data.ConnectionState.Open)
                    con.Close();
                cmd.Dispose();
                adapter.Dispose();
            }
            return dt;
        }

        public DataSet ExecuteDataSet(string spName, string[] Params, object[] Values)
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand();
            DataSet ds = new DataSet();
            try
            {
                cmd = AddCommand(spName, Params, Values);
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);
            }
            catch (Exception ex) { throw (ex); }
            finally
            {
                if (con.State == System.Data.ConnectionState.Open)
                    con.Close();
                cmd.Dispose();
                adapter.Dispose();
            }
            return ds;
        }

        public int ExecuteNonQuery(string spName, string[] Params, object[] Values)
        {
            SqlCommand cmd = new SqlCommand();
            int i = -1;
            try
            {
                cmd = AddCommand(spName, Params, Values);
                AddParameter(cmd, this.FormatParameter("RETURN_VALUE"), 0, StoredProcedureParameterDirection.ReturnValue);
                cmd.ExecuteNonQuery();
                i = (int)cmd.Parameters[this.FormatParameter("RETURN_VALUE")].Value;
            }
            catch (Exception ex) { throw (ex); }
            finally
            {
                if (con.State == System.Data.ConnectionState.Open)
                    con.Close();
                cmd.Dispose();
            }
            return i;
        }

        public object ExecuteScalar(string spName, string[] Params, object[] Values)
        {
            SqlCommand cmd = new SqlCommand();
            object obj = null;
            try
            {
                cmd = AddCommand(spName, Params, Values);
                obj = cmd.ExecuteScalar();
            }
            catch (Exception ex) { throw (ex); }
            finally
            {
                if (con.State == System.Data.ConnectionState.Open)
                    con.Close();
                cmd.Dispose();
            }
            return obj;
        }
        
        #endregion

        enum StoredProcedureParameterDirection
        {
            Input, InputOutput, Output, ReturnValue
        }
    }
}
